package com.asiainfo.kbisheet.util

import groovy.sql.Sql
import groovy.util.logging.Slf4j
import groovy.xml.MarkupBuilder

import java.sql.Connection
import java.util.ArrayList
import java.util.List

import com.asiainfo.kbisheet.vo.ColumnVO
import com.asiainfo.kbisheet.vo.DBTypeVO
import com.asiainfo.kbisheet.vo.DataSourceVO
import com.asiainfo.kbisheet.vo.DimVO
import com.asiainfo.kbisheet.vo.FromToDateVO
import com.asiainfo.kbisheet.vo.KbsSheetVO
import com.asiainfo.kbisheet.vo.LabelValueVO
import com.asiainfo.kbisheet.vo.QueryVO
import com.asiainfo.kbisheet.vo.SettingVO
import com.asiainfo.kbisheet.vo.UserColumnHiddenVO
import com.asiainfo.kbisheet.vo.WhereVO

/**
 * 通用的数据库工具
 * @author kyunra
 *
 */
@Slf4j
abstract class DBUCommon implements DBU{
	DataSourceVO dataSourceVO
	DBTypeVO dbTypeVO

	protected SettingVO settingVO = Setting.settingVO;
	
	private Sql _sql
	
	void setDbTypeVO(DBTypeVO dbTypeVO){
		this.dbTypeVO = dbTypeVO
	}
	
	void setDataSourceVO(DataSourceVO dataSourceVO){
		this.dataSourceVO = dataSourceVO
	}
	
	Sql getSQLInstance(){
		if(_sql == null){
			if(dataSourceVO.isUsepool()){
				Connection conn= DspDBCP.getInstance().getConnection(dataSourceVO);
				_sql = Sql.newInstance(conn);
			}
			else{
				_sql = Sql.newInstance(dataSourceVO.getUrl(), dataSourceVO.getUser(),dataSourceVO.getPassword(), dataSourceVO.getDriverClassName())
			}
		}
		_sql
	} 
	
	ArrayList<LabelValueVO> search(String tableName,String labelColumn,String valueColumn,String searchString){
		def sql = getSQLInstance()
		
		def reqColumn = """
			select ${labelColumn},${valueColumn} from ${tableName} where ${labelColumn} like '%${searchString}%' or ${valueColumn} like '%${searchString}%' order by ${labelColumn},${valueColumn}
		"""
		def vos = new ArrayList<LabelValueVO>()
		
		sql.eachRow( reqColumn as String  ) { row ->
			vos << new LabelValueVO(label:row."${labelColumn}".toString().trim() + '(' + row."${valueColumn}".toString().trim() + ')',value:row."${valueColumn}".toString().trim())
		}
		
		vos
	}
	
	
	List<KbsSheetVO> getAllKbsSheetVOList(){
		def sql = getSQLInstance()
		List<KbsSheetVO> vos = new ArrayList<KbsSheetVO>()
		def req = """
			select * from kbs_sheet
		"""
		sql.eachRow( req as String  ) { row ->
			vos << new KbsSheetVO(sheetId:row.SHEET_ID,sourceKey:row.SOURCE_KEY.trim(),sheetTitle:row.SHEET_TITLE.trim(),databaseName:row.DATABASE_NAME.trim(),tableName:row.TABLE_NAME.trim(),xmlName:row.XML_NAME.trim(),xmlTime:row.XML_TIME)
		}
		vos
	}
	
	/**
	 * 得到下拉列表的数据
	 * @param dimVO
	 * @return
	 */
	List<LabelValueVO> getDimList(DimVO dimVO){
		def sql = getSQLInstance()
		
		def distinct = dimVO.distinct ? "distinct" : "" 
		
		if(dimVO.order == null){
			dimVO.order = dimVO.value
		}
		
		String whereString = getWhereString(dimVO.getWheres(),dimVO.getColumns())
		
		String extendColumns = ""
		/*DB2中排序字段必须出现在选择字段中*/
		if(!dimVO.order.startsWith(dimVO.label) && !dimVO.order.startsWith(dimVO.value)){
			extendColumns = "," + dimVO.order
			/*截断排序关键字*/
			int index = extendColumns.indexOf(" ")
			if(index > 0){
				extendColumns = extendColumns.substring(0, index)
			}
		}
		
		def reqColumn = """
        	select ${distinct} ${dimVO.label},${dimVO.value} ${extendColumns} from ${dimVO.table} ${whereString} order by ${dimVO.order}
		"""
		
		def vos = new ArrayList<LabelValueVO>()
		log.info reqColumn
		
		try{
			sql.eachRow( reqColumn as String  ) { row ->
				vos << new LabelValueVO(label:row."${dimVO.label}".toString().trim(), value:row."${dimVO.value}".toString().trim())
			}
		}
		catch(Throwable t){
			log.error t
			throw t
		}
		
		vos
	}
	
	public KbsSheetVO loadKbsSheetVO4Id(String sheetId){
		def sql = getSQLInstance()
		KbsSheetVO kbsSheetVO
		try{
			def row = sql.firstRow("select * from kbs_sheet where SHEET_ID = ?",[sheetId])
			if(row!=null)
				kbsSheetVO = mapRow(row)
		}
		catch(Throwable t){			
			log.error t
			throw t
		}
		kbsSheetVO
	}
	
	def mapRow(row){
		def kbsSheetVO = new KbsSheetVO (sheetId:row.SHEET_ID,sourceKey:row.SOURCE_KEY,sheetTitle:row.SHEET_TITLE,databaseName:row.DATABASE_NAME,tableName:row.TABLE_NAME,xmlName:row.XML_NAME,xmlTime:row.XML_TIME)
		kbsSheetVO
	}
	
	FromToDateVO getDimDate(DimVO dimVO){
		def sql = getSQLInstance()
		
		FromToDateVO fromToDateVO
		try{
			if(dimVO.getSelf()){
				def reqColumn = """
		        	select max(${dimVO.column}) as toDate,min(${dimVO.column}) as fromDate from ${dimVO.table}		
				"""
				log.info reqColumn
				def row = sql.firstRow(reqColumn as String)
				if(row != null){
					fromToDateVO = new FromToDateVO(fromDate:row.fromDate.toString().trim(),toDate:row.toDate.toString().trim())
				}
			}
			else{
				String whereString = getUpperWhereString(dimVO.getWheres(),dimVO.getColumns())
				
				def reqColumn = """
		        	select ${dimVO.from} as ${dimVO.from},${dimVO.to} as ${dimVO.to} from ${dimVO.table} ${whereString}			
				"""	
				
				log.info reqColumn
				def row = sql.firstRow(reqColumn as String)
				if(row != null){
					println row.getClass()
					println row.rpt_begin_date
					fromToDateVO = new FromToDateVO(fromDate:row."${dimVO.from}".toString().trim(),toDate:row."${dimVO.to}".toString().trim())
				}
			}
		}
		catch(Throwable t){
			log.error t
			t.printStackTrace()
			throw t
		}
		fromToDateVO
	}
	
	String getDataXML(QueryVO queryVO){ 
		def sql = getSQLInstance()
		/*拼column字符串*/
		String columnString = getColumnString(queryVO, false)     	
		
		/*拼where字符串*/
		String whereString = getWhereString(queryVO.getWheres(),queryVO.getColumns())       
		
		String orderString = getOrderString(queryVO)
		
		String from = queryVO.getSql()
		
		if(queryVO.getSql()==null || queryVO.getSql().length() == 0){
			from = "${queryVO.dataBaseName}.${queryVO.tableName}"
		}
		else{
			from = "(${queryVO.sql})"
		}
		
		def req = getPageSQL(queryVO, columnString,orderString, whereString, from)
		log.info req	
		
		def writer = new StringWriter()
		def xml = new MarkupBuilder( writer )
		
		/*最大记录数*/
		def maxCountResult = sql.firstRow("select count(*) as maxCount from ${from} ${whereString}" as String)
		def maxCount = maxCountResult.maxCount
		log.info "maxCount = " + maxCount
		xml.rows(maxCount:maxCount) {
			sql.eachRow( req as String  ) { row ->
				xml.row {
					queryVO.columns.each { column ->
						xml."${column.name}"(row."${column.name}")
					}
				}
			}
		}
		writer.toString()
	}
	
	abstract String getPageSQL(queryVO, columnString,orderString, whereString, from);
	
	/**
	 * 保存一组设置，先删除所有设置，再添加
	 * @param userColumnHiddenVO
	 * @return
	 */
	boolean saveUserColumnSets(UserColumnHiddenVO userColumnHiddenVO){
		def sql = getSQLInstance()
		def reqColumn = """
        	delete from KBS_USER_COLUMN where SHEET_ID = ${userColumnHiddenVO.sheetId} and USER_ID = ${userColumnHiddenVO.userId}
		"""
		sql.execute(reqColumn)
		
		userColumnHiddenVO.columnUUIDs.each { columnUUID->
			reqColumn = """
	        	insert into KBS_USER_COLUMN(USER_ID, SHEET_ID, COLUMN_UUID) values(${userColumnHiddenVO.userId}, ${userColumnHiddenVO.sheetId}, ${columnUUID})
			"""
			sql.execute(reqColumn)
		}
		
		true
	}
	
	boolean addUserColumnSet(UserColumnHiddenVO userColumnHiddenVO){
		def sql = getSQLInstance()
		def reqColumn = """
        	insert into KBS_USER_COLUMN(USER_ID, SHEET_ID, COLUMN_UUID) values(${userColumnHiddenVO.userId}, ${userColumnHiddenVO.sheetId}, ${columnUUID})
		"""
		sql.execute(reqColumn)  
		
		true
	}
	
	List<String> loadUserColumnSet(String userId,String sheetId){
		def sql = getSQLInstance()
		List<String> columnUUIDs = new ArrayList<String>()
		def req = """
        	select COLUMN_UUID from KBS_USER_COLUMN where SHEET_ID = ${sheetId} and USER_ID = ${userId}
		"""
		sql.eachRow( req) { row ->
			columnUUIDs.add(row.COLUMN_UUID)
		}
		columnUUIDs
	}
	
	
	
	String getWhereString(ArrayList<WhereVO> wheres,ArrayList<ColumnVO> columns){
		/*拼where字符串*/
		String whereString = ""
		String linkString = "where"
		wheres.each {whereVO ->
			ColumnVO columnVO = columns.find{column->column.name.trim().equalsIgnoreCase(whereVO.column.trim())}
			log.info(whereVO.column + ":" + (columnVO == null?'无列信息':(columnVO.type)))
			if(columnVO != null && settingVO.dates.contains(columnVO.type)){
				whereString = whereString + " " + linkString + " " + toDateString(whereVO)
			}
			else if(columnVO != null && settingVO.numbers.contains(columnVO.type)){
				whereString = whereString + " " + linkString + " " + whereVO.toNumberString()
			}
			else{
				whereString = whereString + " " + linkString + " " + whereVO
			}
			linkString = "and"
		}
		whereString
	}
	
	String getUpperWhereString(ArrayList<WhereVO> wheres,ArrayList<ColumnVO> columns){
		/*拼where字符串*/
		String whereString = ""
		String linkString = "where"
		wheres.each {whereVO ->
			ColumnVO columnVO = columns.find{column->column.name.trim().equalsIgnoreCase(whereVO.column.trim())}
			log.info(whereVO.column + ":" + (columnVO == null?'无列信息':(columnVO.type)))
			if(columnVO != null && settingVO.dates.contains(columnVO.type)){
				whereString = whereString + " " + linkString + " " + toDateString(whereVO)
			}
			else if(columnVO != null && settingVO.numbers.contains(columnVO.type)){
				whereString = whereString + " " + linkString + " " + whereVO.toNumberString()
			}
			else{
				whereString = whereString + " " + linkString + " " + whereVO.toUpperString()
			}
			linkString = "and"
		}
		whereString
	}
	
	abstract String toDateString(WhereVO whereVO);
	
	String getColumnString(QueryVO queryVO, boolean allExport){
		String columnString = ""
		queryVO.columns.each { column ->
			String alias = column.name
			if(allExport){
				if(column.longTitle !=null && column.longTitle.length() > 0){
					alias = column.longTitle
				}
				else if(column.title !=null && column.title.length() > 0){
					alias = column.title
				}
				
				/*过滤字符*/
				alias = alias.replaceAll("\\(", "（")
				alias = alias.replaceAll("\\)", "）")
			}
			
			String temp = column.name
			if(column.sql != null){
				temp = "(" + column.sql + ")"
			}            
			else if(settingVO.dates.contains(column.type)){
				temp = formatDateColumn(column.name)
			}
			
			if(!temp.equals(alias)){
				temp = temp + " as \"" + alias + "\""
			}
			columnString = columnString + temp + ","
		}
		/*去掉最后一个,*/
		columnString = columnString.substring(0, columnString.length() - 1)
		columnString
	}
	
	String getOrderString(QueryVO queryVO){
		String orderString = ""
		if(queryVO.order != null && !queryVO.order.isEmpty()){
			orderString = " order by " + queryVO.order;
		}
		orderString
	}
	
	protected String trim(String column){	
		if(column != null)
			column = column.trim()        
		column
	}	
	
	public void close() {
		if(_sql != null){
			try{
				_sql.getConnection().close();
			}
			catch(Throwable e){
			}
		}
	}
}
